package com.saic.parse.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.saic.parse.IImportAmong;

public class ImportModelPrice implements IImportAmong {

	@Override
	public void importExcel(Connection connBartack, Connection connDataParsedb)throws Exception {
		

		connBartack.createStatement().execute("delete from t_velmodel_price");
		ResultSet result = connDataParsedb.createStatement().executeQuery("select price.*,dealer.city_web_id from t_autohome_vel_model_price20140305 as price,t_autohome_dealer as dealer where price.dealer_id=dealer.web_id and dealer.city_web_id='shanghai'");
		
		while(result.next()){
			//取出基本数据
			Integer dealerId = result.getInt(3);
			String source = "autohome";
			Integer sourceId = 0;
			double guiding_price = new Double(result.getString(5).replace(" ",""));
			double current_price = new Double(result.getString(6).replace(" ",""));
			double preferential_price = new Double(result.getString(7).replace(" ",""));
			String velModelId = result.getString(4);
			String velSeriesId = null;
			String cityId = result.getString(9);
			String updateTime = result.getString(8);
			ResultSet resultCity = connBartack.createStatement().executeQuery("select id from t_city where cityCode='"+cityId+"'");
			while(resultCity.next()){
				cityId = resultCity.getString(1);
			}
			resultCity.close();

			ResultSet resultModel = connBartack.createStatement().executeQuery("select id,velSeriesId from t_velModel where web_id='"+velModelId+"'");
			velSeriesId = null;
			while(resultModel.next()){
				velModelId = resultModel.getString(1);
				velSeriesId =  resultModel.getString(2);
			}
			resultModel.close();
			ResultSet resultDealer = connBartack.createStatement().executeQuery("select id from t_dealer where web_id='"+dealerId+"'");
			System.out.println(dealerId);

			dealerId = null;
			while(resultDealer.next()){

				dealerId =  resultDealer.getInt(1);
			}
			
			//取出对方表的cityid
			if(dealerId != null){
				PreparedStatement model_price_prepare = connBartack.prepareStatement("insert into t_velmodel_price(dealerId,velmodelid,source,sourceid,guiding_price,current_price,preferential_price,velseriesId,cityId,updateTime)values(?,?,?,?,?,?,?,?,?,?)");
				model_price_prepare.setInt(1, dealerId);
				model_price_prepare.setString(2, velModelId);
				model_price_prepare.setString(3, source);
				model_price_prepare.setInt(4, sourceId);
				model_price_prepare.setDouble(5, guiding_price);
				model_price_prepare.setDouble(6, current_price);
				model_price_prepare.setDouble(7, preferential_price);
				model_price_prepare.setString(8, velSeriesId);
				model_price_prepare.setString(9, cityId);
				model_price_prepare.setString(10, updateTime);
				model_price_prepare.execute();
				model_price_prepare.close();
			}
		}
		connBartack.createStatement().execute("delete from t_velmodel_price where velSeriesId is null;");

	}
	
	public static void main(String[] args)throws Exception {

		
		String dataParseSql = "jdbc:mysql://192.168.22.70:3306/dataparser?"
	      + "user=root&password=root&useUnicode=true&characterEncoding=UTF8";
		Class.forName("com.mysql.jdbc.Driver");
		Connection connDataParse = DriverManager.getConnection(dataParseSql);
		
		String bartackSql = "jdbc:mysql://192.168.22.70:3306/bartack?"
		      + "user=root&password=root&useUnicode=true&characterEncoding=UTF8";
			Class.forName("com.mysql.jdbc.Driver");

		Connection connBartack = DriverManager.getConnection(bartackSql);
		
		ImportModelPrice imp = new ImportModelPrice();
		connDataParse.setAutoCommit(false);
		connBartack.setAutoCommit(false);
		imp.importExcel(connBartack,connDataParse);
		connDataParse.commit();
		connBartack.commit();
		
	
	}
	
}
